# import xlrd
# import pyecharts.options as opts
# data=xlrd.open_workbook(r'C:\Users\chumenliuxiang\Desktop\实验室作业\80%.xls','r')
# biao1=data.sheet_by_name('Sheet1')
# tv=biao1.col_values(0)
# radio=biao1.col_values(1)
# newspaper=biao1.col_values(2)
# sales=biao1.col_values(3)
# tv=tv[1:]
# radio=radio[1:]
# newspaper=newspaper[1:]
# sales=sales[1:]
import numpy as np
import pandas as pd
import statsmodels.api as sm

file = r'C:\Users\chumenliuxiang\Desktop\实验室作业\80%.xls'
data = pd.read_excel(file)
data.columns = ['tv', 'radio', 'newspaper', 'sales']

x = sm.add_constant(data.iloc[:,1:]) #生成自变量
y = data['sales'] #生成因变量
model = sm.OLS(y, x) #生成模型
result = model.fit() #模型拟合
result.summary() #模型描述


def looper(limit):
    cols = ['tv', 'radio', 'newspaper', 'sales']
    for i in range(len(cols)):
        data1 = data[cols]
        x = sm.add_constant(data1)  # 生成自变量
        y = data['sales']  # 生成因变量
        model = sm.OLS(y, x)  # 生成模型
        result = model.fit()  # 模型拟合
        pvalues = result.pvalues  # 得到结果中所有P值
        pvalues.drop('const', inplace=True)  # 把const取得
        pmax = max(pvalues)  # 选出最大的P值
        if pmax > limit:
            ind = pvalues.idxmax()  # 找出最大P值的index
            cols.remove(ind)  # 把这个index从cols中删除
        else:
            return result


result = looper(0.05)
print(result.summary())